package com.qf.framwork.base;

import com.qf.framwork.annotation.TableField;
import com.qf.framwork.page.Page;
import com.qf.framwork.page.PageThreadLocal;
import com.qf.framwork.utils.ConnectionUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * description:
 * author: Ken
 * 公众号：Java架构栈
 */
public class BaseDao<T> {

    /**
     * 用于删除和修改
     * @return
     */
    public int update(String sql, Object...params){
        Connection connection = ConnectionUtils.getConnection();
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            //设置参数
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            //执行sql
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionUtils.close(connection, ps, null);
        }
        return -1;
    }

    /**
     * 用于增加
     * @return
     */
    public int insert(String sql, Object...params){
        Connection connection = ConnectionUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            //参数二，用于获取自增的id
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            //设置参数
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            //执行sql
            ps.executeUpdate();

            //再次获取自增的id
            resultSet = ps.getGeneratedKeys();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionUtils.close(connection, ps, resultSet);
        }
        return -1;
    }

    /**
     * 查询列表
     * @return
     */
    public List<T> queryList(String sql, Class<T> cls, Object...params){
        Connection connection = ConnectionUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        List<T> tList = new ArrayList<>();
        try {
            ps = connection.prepareStatement(sql);
            //设置参数
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            //执行查询
            resultSet = ps.executeQuery();

            //根据查询结果封装结果集
            while(resultSet.next()){
                //创建一个T类型的对象
                T t = cls.newInstance();
                //根据实体类的属性依次去取值
                Field[] fields = cls.getDeclaredFields();
                //循环处理
                for (Field field : fields) {
                    //授权
                    field.setAccessible(true);
                    try {
                        //对应的列名
                        String columName = field.getName();
                        //判断是否存在注解
                        TableField tableField = field.getAnnotation(TableField.class);
                        if (tableField != null){
                            columName = tableField.value();
                        }

                        //以属性的名字作为列的名称去取查询的结果
                        Object value = resultSet.getObject(columName);
                        //将对应的值设置到属字段中
                        field.set(t, value);
                    } catch (Exception e) {
                    }
                }
                //封装结果集
                tList.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectionUtils.close(connection, ps, resultSet);
        }
        return tList;
    }

    /**
     * 结果集自定义的查询
     */
    public <S> S query(String sql, QueryCallback<S> queryCallback, Object...params){
        Connection connection = ConnectionUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        try {
            ps = connection.prepareStatement(sql);
            //设置参数
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            //执行查询
            resultSet = ps.executeQuery();
            //使用调用者传过来的queryCallback对象，对ResultSet进行处理
            return queryCallback.resultSetHandler(resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectionUtils.close(connection, ps, resultSet);
        }
        return null;
    }

    /**
     * 查询单条记录
     * @param sql
     * @param cls
     * @param params
     * @return
     */
    public T queryOne(String sql, Class<T> cls, Object...params){
        List<T> datas = queryList(sql, cls, params);
        return datas.size() > 0 ? datas.get(0) : null;
    }

    /**
     * 分页查询
     * @return
     */
    public List<T> queryPage(String sql, Class<T> cls, Object...params){
        //强制将SQL转换成小写
        sql = sql.toLowerCase();

        //1、获取Page对象 - ThreadLocal
        Page page = PageThreadLocal.getPage();
        //如果没有找到分页对象，则查询全部
        if (page == null) return this.queryList(sql, cls, params);

        //2、查询总条数 select count(*) from xxxx
        //自己构建一个查询总条数的sql
        String sqlSum = "select count(*) " + sql.substring(sql.indexOf(" from "));
        System.out.println("---->分页查询总条数的SQL：" + sqlSum);
        //3、执行查询总条数的sql语句
        int pageSum = this.query(sqlSum, resultSet -> resultSet.next() ? resultSet.getInt(1) : 0, params);

        //4、设置总条数 计算总页码
        page.setPageSum(pageSum);
        int pageCount = page.getPageSum() % page.getPageSize() == 0 ?
                page.getPageSum() / page.getPageSize() :
                page.getPageSum() / page.getPageSize() + 1;
        page.setPageCount(pageCount);

        //5、分页查询
        sql += " limit ?,?";

        //构造参数
        List<Object> newParams = new ArrayList<>();
        if (params != null){
            //Arrays.asList - 转换的List集合 是不能往里加东西的
            List<Object> oldParams = Arrays.asList(params);
            newParams.addAll(oldParams);
        }
        //设置分页参数
        newParams.add((page.getPage() - 1) * page.getPageSize());
        newParams.add(page.getPageSize());

        System.out.println("--->分页的SQL：" + sql);
        List<T> datas = this.queryList(sql, cls, newParams.toArray());
        //设置结果集到Page对象中
        page.setDatas(datas);
        return datas;
    }

    /**
     * 命令模式的接口
     */
    public static interface QueryCallback<S>{

        S resultSetHandler(ResultSet resultSet) throws SQLException;
    }
}
